![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Spread Out Random I/Os By the very nature of random I/Os, accesses are to vastly different places in the Oracle data files. This pattern makes it easy for random I/O problems to be alleviated by simply adding more disks to the system and spreading the Oracle tables across these disks. You can do this by striping the data across multiple drives or (depending on your configuration) by simply putting tables on different drives. Striping is the act of transparently dividing the contents of a large data source into smaller sources. Striping can be done through Oracle, the OS, or through hardware disk arrays. Oracle Striping Oracle striping involves dividing a tables data into small pieces and further dividing these pieces among different data files. Oracle striping is done at the tablespace level with the CREATE TABLESPACE command. To create a striped tablespace, use a command similar to this one: SQL> CREATE TABLESPACE mytablespace 2 DATAFILE 'file1.dbf' SIZE 500K, 3 'file2.dbf' SIZE 500K, 4 'file3.dbf' SIZE 500K, 5 'file4.dbf' SIZE 500K; Tablespace created. To complete this task, you must then create a table within this tablespace with four extents. This creates the table across all four data files, which (hopefully) are each on their own disk. Create the table with a command like this one: SQL> CREATE TABLE mytable 2 ( name varchar(40), 3 title varchar(20), 4 office_number number(4) ) 5 TABLESPACE mytablespace 6 STORAGE ( INITIAL 495K NEXT 495K 7 MINEXTENTS 4 PCTINCREASE 0 ); Table created. In this example, each data file has a size of 500K. This is called the stripe size. If the table is large, the stripes are also large (unless you add many stripes). Large stripes can be an advantage when you have large pieces of data within the table, such as BLOBs. In most OLTP applications, it is more advantageous to have a smaller striping factor to distribute the I/Os more evenly. The size of the data files depends on the size of your tables. Because it is difficult to manage hundreds of data files, it is not uncommon to have one data file per disk volume per tablespace. If your database is 10 gigabytes in size and you have 10 disk volumes, your data file size will be 1 gigabyte. When you add more data files of a smaller size, your I/Os are distributed more evenly, but the system is harder to manage because there are more files. As you see in Chapter 15, Disk Arrays, you can achieve both manageability and ease of use by using a hardware or software disk array. Oracle striping can be used in conjunction with OS or hardware striping. OS Striping Depending on the operating system, striping can be done at the OS level either through an operating system facility or through a third-party application. OS striping is done at OS installation time. OS disk striping is done by taking two or more disks and creating one large logical disk. In sequence, the stripes appear on the first disk, then the second disk, and so on (see Figure 9.1). The size of each stripe depends on the OS and the striping software you are running.
To figure out which disk has the desired piece of data, the OS must keep track of where the data is. To do this, a certain amount of CPU time must be spent maintaining this information. If fault tolerance is used, even more CPU resources are required. Depending on the software you are using to stripe the disks, the OS monitoring facilities may display disk I/O rates on a per-disk basis or on a per-logical-disk basis. Regardless of how the information is shown, you can easily determine the I/O rate per disk. Many of the OS-striping software packages on the market today can also take advantage of RAID technology to provide a measure of fault tolerance. OS striping is very good; however, it does consume system resources that hardware striping does not.
Hardware Striping Hardware striping has a similar effect to OS striping. Hardware fault tolerance is obtained by replacing your disk controller with a disk array. A disk array is a controller that uses many disks to make one logical disk. The system takes a small slice of data from each of the disks in sequence to make up the larger logical disk (see Figure 9.2).
As you can see in Figures 9.1 and 9.2, to the user and the RDBMS software, the effect is the same whether you use OS or hardware disk striping. The main difference between the two is where the actual overhead of maintaining the disk array is maintained. Hardware fault tolerance has the advantage of not taking any additional CPU or memory resources on the server. All the logic to do the striping is done at the controller level. As with OS striping, hardware striping can also take advantage of RAID technology for fault tolerance.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement. |